package benchmarks.tpch.mongo.queries;

import api.Configuration;
import api.connection.ConnectionMongo;
import api.connection.DBConnection;
import api.result.Result;
import com.mongodb.*;
import com.mongodb.client.AggregateIterable;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import org.bson.BsonDocument;
import org.bson.Document;

import java.util.ArrayList;

public class Q22Mongo extends GenericQueryMongo {
    public static final String queryName = "Q22";

    public Q22Mongo(){
        super.queryName = queryName;
    }

    private static String phone = "{ $or : [ " +
            "{ \"order.customer.phone\" : { $regex : '^13' } }, " +
            "{ \"order.customer.phone\" : { $regex : '^31' } }, " +
            "{ \"order.customer.phone\" : { $regex : '^23' } }, " +
            "{ \"order.customer.phone\" : { $regex : '^29' } }, " +
            "{ \"order.customer.phone\" : { $regex : '^30' } }, " +
            "{ \"order.customer.phone\" : { $regex : '^18' } }, " +
            "{ \"order.customer.phone\" : { $regex : '^17' } } " +
            "]}";

    private static String matchPhoneStringQuery = "{ $match : " +
            phone +
            "}";

    private static String calculateMatchStringQuery = "{ $match : " +
            "{ \"order.customer.acctbal\" : { \"$gt\" : 0 } }" +
            "}";

    private static String calculateGroupStringQuery = "{ $group : {" +
            "_id : \"avg_acc\", " +
            "avg : { $avg : \"$order.customer.acctbal\"} " +
            "}}";

    private static String firstGroupStringQuery = "{ $group : { " +
            "_id : \"$order.customer.custkey\",\n" +
            "bal : { $first : \"$order.customer.acctbal\" }, " +
            "phone : { $first : \"$order.customer.phone\" } " +
            "}}";

    private static String secondGroupStringQuery = "{ $group : {" +
            "_id : { \"$substr\" : [ \"$phone\", 0, 2] }, " +
            "numcust : { \"$sum\" : 1 }, " +
            "totacctbal : { \"$sum\" : \"$bal\"} " +
            "}}";

    private static String sortStringQuery = "{ $sort : { \"_id\" : 1 }}";

    public Result run(DBConnection dbConnection) {
        ConnectionMongo connectionMongo = (ConnectionMongo)dbConnection;
        MongoClient mongoClient = connectionMongo.makeConnection();
        MongoDatabase database = mongoClient.getDatabase("tpch");
        ArrayList<BsonDocument> calculateAggregate = new ArrayList<>();
        calculateAggregate.add(BsonDocument.parse(matchPhoneStringQuery));
        calculateAggregate.add(BsonDocument.parse(calculateMatchStringQuery));
        calculateAggregate.add(BsonDocument.parse(calculateGroupStringQuery));
        AggregateIterable<Document> result = database.getCollection("deals").aggregate(calculateAggregate);
        MongoCursor<Document> iterator = result.iterator();
        ArrayList<Document> results = new ArrayList<>();
        while (iterator.hasNext()) {
            results.add(iterator.next());
        }
        double avgAccBal = results.get(0).getDouble("avg");

        String matchStringQuery = "{ $match : { " +
                "\"order.customer.acctbal\" : { \"$gt\" : " +
                avgAccBal +
                " } " +
                "}}";
        ArrayList<BsonDocument> aggregateList = new ArrayList<>();
        aggregateList.add(BsonDocument.parse(matchPhoneStringQuery));
        aggregateList.add(BsonDocument.parse(matchStringQuery));
        aggregateList.add(BsonDocument.parse(firstGroupStringQuery));
        aggregateList.add(BsonDocument.parse(secondGroupStringQuery));
        aggregateList.add(BsonDocument.parse(sortStringQuery));
        result = database.getCollection("deals").aggregate(aggregateList);
        iterator = result.iterator();
        results = new ArrayList<>();
        while (iterator.hasNext()) {
            results.add(iterator.next());
        }
        return null;
    }

//    @Override
//    public Result run(DBConnection dbConnection) {
//        MongoClient mongoClient = (MongoClient)dbConnection.makeConnection();
//        String databaseName = "tpch";
//        String collectionName = "deals";
//
//        int[] countryCode = {13, 31, 23, 29, 30, 18, 17};
//
//        DB db = mongoClient.getDB(databaseName);
//        DBCollection collection = db.getCollection(collectionName);
//
//        Pattern[] pattern = new Pattern[7];
//        BasicDBObject[] phoneNumber = new BasicDBObject[7];
//        for(int i = 0; i < 7; i ++){
//            String country = "" + countryCode[i];
//            pattern[i] = Pattern.compile("^" + country);
//            phoneNumber[i] = new BasicDBObject();
//            phoneNumber[i].append("order.customer.phone", new BasicDBObject("$regex", pattern[i]));
//        }
//        BasicDBObject phone = new BasicDBObject("$or", phoneNumber);
//
//        BasicDBObject match = new BasicDBObject("order.customer.acctbal", new BasicDBObject("$gt", 0));
//
//        BasicDBObject group = new BasicDBObject();
//        group.append("_id", "avg_acc").append("avg", new BasicDBObject("$avg", "$order.customer.acctbal"));
//
//        AggregationOutput output = collection.aggregate(
//                new BasicDBObject("$match", phone), new BasicDBObject("$match", match), new BasicDBObject("$group", group));
//        Iterable<DBObject> list = output.results();
//        Object avgAccBal = new Object();
//        for(DBObject dbObject:list){
//            avgAccBal = dbObject.get("avg");
//        }
//
//        BasicDBObject match2 = new BasicDBObject("order.customer.acctbal", new BasicDBObject("$gt", avgAccBal));
//
//        BasicDBObject group2 = new BasicDBObject();
//        group2.append("_id", "order.customer.custkey")
//                .append("bal", new BasicDBObject("$first", "$order.customer.acctbal"))
//                .append("phone", new BasicDBObject("$first", "$order.customer.phone"));
//
//        Object[] substr = new Object[3];
//        substr[0] = "$phone";
//        substr[1] = 0;
//        substr[2] = 2;
//        BasicDBObject group3 = new BasicDBObject();
//        group3.append("_id", new BasicDBObject("$substr", substr))
//                .append("numcust", new BasicDBObject("$sum", 1))
//                .append("totacctbal", new BasicDBObject("$sum", "$bal"));
//
//        AggregationOutput output2 = collection.aggregate(
//                new BasicDBObject("$match", phone), new BasicDBObject("$match", match2), new BasicDBObject("$group", group2),
//                new BasicDBObject("$group", group3), new BasicDBObject("$sort", new BasicDBObject("_id", 1)));
//        /*Iterable<DBObject> list2 = output2.results();
//        for(DBObject dbObject:list2){
//            System.out.println(dbObject.toString());
//        }*/
//        return null;
//    }

    private static class TestQ22{
        public static void main(String[] args){
            Configuration config = new Configuration();
            config.setBenchmark("tpch");
            config.setDb_username("root");
            config.setDb_password("123456");
            Q22Mongo q22Mongo = new Q22Mongo();
            DBConnection dbConnection =new ConnectionMongo(config);
            q22Mongo.run(dbConnection);
        }
    }
}
